package com.news.dao;

import com.news.model.News;
import com.news.model.Sort;
import com.news.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;

public class NewsDao {
    private JdbcTemplate jdbcTemplate = null;
    
    public NewsDao() {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    }

    // 获取推荐新闻（图片新闻）
    public List<News> getRecommendNews(int sort_id) {
        try {
            String sql = "SELECT * FROM tb_news WHERE sort_id = ? AND img_url IS NOT NULL ORDER BY newTime DESC LIMIT 3";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), sort_id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 获取要闻聚焦新闻列表
    public List<News> getFocusNews(int sort_id) {
        try {
            String sql = "SELECT * FROM tb_news WHERE sort_id = ? ORDER BY newTime DESC LIMIT 9";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), sort_id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 获取深度观察新闻
    public List<News> getDeepNews(int sort_id) {
        try {
            String sql = "SELECT * FROM tb_news WHERE sort_id = ? AND img_url IS NOT NULL ORDER BY newTime DESC LIMIT 4";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), sort_id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 获取新闻列表（通用方法）
    public List<News> getNewsList(int sort_id, int limit) {
        try {
            String sql = "SELECT * FROM tb_news WHERE sort_id = ? ORDER BY newTime DESC LIMIT ?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), sort_id, limit);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 获取热门新闻
    public List<News> getHotNews(int sort_id) {
        try {
            String sql = "SELECT * FROM tb_news WHERE sort_id = ? AND img_url IN ('20.png', '21.png', '22.png') ORDER BY FIELD(img_url, '20.png', '21.png', '22.png') LIMIT 3";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), sort_id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 根据ID获取新闻详情
    public News getNewsById(int new_id) {
        try {
            String sql = "SELECT * FROM tb_news WHERE new_id = ?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(News.class), new_id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 获取相关新闻
    public List<News> getRelatedNews(int sort_id, int currentnew_id) {
        try {
            String sql = "SELECT * FROM tb_news WHERE sort_id = ? AND new_id != ? ORDER BY newTime DESC LIMIT 5";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), sort_id, currentnew_id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 搜索新闻
    public List<News> searchNews(String keyword) {
        try {
            String sql = "SELECT * FROM tb_news WHERE title LIKE ? OR content LIKE ? ORDER BY newTime DESC";
            String searchPattern = "%" + keyword + "%";
            return jdbcTemplate.query(
                sql, 
                new BeanPropertyRowMapper<>(News.class),
                searchPattern,
                searchPattern
            );
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 获取新闻总数（支持搜索）
    public int getNewsCount(String keyword) {
        try {
            String sql = "SELECT COUNT(*) FROM tb_news";
            if (keyword != null && !keyword.trim().isEmpty()) {
                sql += " WHERE title LIKE ? OR content LIKE ?";
                String pattern = "%" + keyword.trim() + "%";
                return jdbcTemplate.queryForObject(sql, Integer.class, pattern, pattern);
            }
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    // 分页获取新闻列表（支持搜索）
    public List<News> getNewsByPage(int offset, int pageSize, String keyword) {
        try {
            String sql = "SELECT * FROM tb_news";
            if (keyword != null && !keyword.trim().isEmpty()) {
                sql += " WHERE title LIKE ? OR content LIKE ?";
                sql += " ORDER BY newTime DESC LIMIT ?, ?";
                String pattern = "%" + keyword.trim() + "%";
                return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), 
                    pattern, pattern, offset, pageSize);
            }
            sql += " ORDER BY newTime DESC LIMIT ?, ?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(News.class), 
                offset, pageSize);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    // 获取所有分类
    public List<Sort> getAllSorts() {
        try {
            String sql = "SELECT * FROM tb_sort ORDER BY sort_id";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Sort.class));
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    // 添加新闻
    public boolean addNews(News news) {
        try {
            String sql = "INSERT INTO tb_news (sort_id, title, content, newTime, writer, img_url) VALUES (?, ?, ?, ?, ?, ?)";
            int rows = jdbcTemplate.update(sql, 
                news.getSort_id(),
                news.getTitle(),
                news.getContent(),
                news.getNewTime(),
                news.getWriter(),
                news.getImg_url()
            );
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    // 更新新闻
    public boolean updateNews(News news) {
        try {
            String sql = "UPDATE tb_news SET sort_id=?, title=?, content=?, writer=?, img_url=? WHERE new_id=?";
            int rows = jdbcTemplate.update(sql,
                news.getSort_id(),
                news.getTitle(),
                news.getContent(),
                news.getWriter(),
                news.getImg_url(),
                news.getNew_id()
            );
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除新闻
    public boolean deleteNews(int newsId) {
        try {
            String sql = "DELETE FROM tb_news WHERE new_id=?";
            int rows = jdbcTemplate.update(sql, newsId);
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public int getTotalNewsCount() {
        try {
            String sql = "SELECT COUNT(*) FROM tb_news";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public int getTotalUserCount() {
        try {
            String sql = "SELECT COUNT(*) FROM tb_admin";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public int getTotalSortCount() {
        try {
            String sql = "SELECT COUNT(*) FROM tb_sort";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public List<Sort> getSortsByPage(int offset, int pageSize) {
        try {
            String sql = "SELECT * FROM tb_sort ORDER BY sort_id LIMIT ?, ?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Sort.class), 
                offset, pageSize);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public int getNewsBySortId(int sortId) {
        try {
            String sql = "SELECT COUNT(*) FROM tb_news WHERE sort_id = ?";
            return jdbcTemplate.queryForObject(sql, Integer.class, sortId);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public boolean deleteSort(int sortId) {
        try {
            String sql = "DELETE FROM tb_sort WHERE sort_id = ?";
            int rows = jdbcTemplate.update(sql, sortId);
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public Sort getSortById(int sortId) {
        try {
            String sql = "SELECT * FROM tb_sort WHERE sort_id = ?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Sort.class), sortId);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean addSort(Sort sort) {
        try {
            String sql = "INSERT INTO tb_sort(name) VALUES(?)";
            int rows = jdbcTemplate.update(sql, sort.getName());
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateSort(Sort sort) {
        try {
            String sql = "UPDATE tb_sort SET name = ? WHERE sort_id = ?";
            int rows = jdbcTemplate.update(sql, sort.getName(), sort.getSort_id());
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 搜索分类并分页
     */
    public List<Sort> searchSortsByPage(String keyword, int offset, int pageSize) {
        String sql = "select * from tb_sort where name like ? limit ?, ?";
        return jdbcTemplate.query(sql, 
            new BeanPropertyRowMapper<>(Sort.class),
            "%" + keyword + "%", offset, pageSize);
    }

    /**
     * 获取搜索结果的总数
     */
    public int getTotalSearchSortCount(String keyword) {
        String sql = "select count(*) from tb_sort where name like ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, "%" + keyword + "%");
    }
} 